﻿using Dapper;
using NiuX.Objects;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using MoreLinq.Extensions;
using KeyAttribute = System.ComponentModel.DataAnnotations.KeyAttribute;
using TableAttribute = System.ComponentModel.DataAnnotations.Schema.TableAttribute;
using Microsoft.Data.SqlClient;
using NiuX.Dapper.Attributes;

namespace NiuX.Dapper.Dapper;

public static class DapperContribExtensions
{

    public static void BulkInsert<T>(this IDbConnection conn, List<T> items)
    {
        if (items.IsNullOrEmpty())
        {
            return;
        }

        if (conn is not SqlConnection sqlConnection)
        {
            throw new Exception("暂不支持");
        }

        if (sqlConnection.State == ConnectionState.Closed)
        {
            sqlConnection.Open();

        }

        var sqlBulkCopy = CreateSqlBulkCopy(sqlConnection, items);
        sqlBulkCopy.WriteToServer(items.ToDataTable());
    }

    public static async Task BulkInsertAsync<T>(this IDbConnection conn, List<T> items)
    {
        if (items.IsNullOrEmpty())
        {
            return;
        }

        if (conn is not SqlConnection sqlConnection)
        {
            throw new Exception("暂不支持");
        }

        if (sqlConnection.State == ConnectionState.Closed)
        {
            await sqlConnection.OpenAsync();

        }

        var sqlBulkCopy = CreateSqlBulkCopy(sqlConnection, items);
        await sqlBulkCopy.WriteToServerAsync(items.ToDataTable());
    }

    public static async Task BulkInsertAsync<T>(this IDbConnection conn, List<T> items, SqlBulkCopy sqlBulkCopy)
    {
        if (items.IsNullOrEmpty())
        {
            return;
        }

        if (conn is not SqlConnection sqlConnection)
        {
            throw new Exception("暂不支持");
        }

        if (sqlConnection.State == ConnectionState.Closed)
        {
            await sqlConnection.OpenAsync();

        }

        await sqlBulkCopy.WriteToServerAsync(items.ToDataTable());
    }

    public static async Task BulkInsertExcludeAsync<T>(this IDbConnection conn, List<T> items, List<string> columns)
    {
        if (items.IsNullOrEmpty())
        {
            return;
        }

        if (conn is not SqlConnection sqlConnection)
        {
            throw new Exception("暂不支持");
        }

        if (sqlConnection.State == ConnectionState.Closed)
        {
            await sqlConnection.OpenAsync();
        }

        var sqlBulkCopy = CreateSqlBulkCopy(sqlConnection, items, columns);
        await sqlBulkCopy.WriteToServerAsync(items.ToDataTable());
    }


    private static SqlBulkCopy CreateSqlBulkCopy<T>(SqlConnection sqlConnection, List<T> items, List<string>? ignoreColumns = null)
    {
        ignoreColumns ??= new List<string>();

        var sqlBulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, null);
        sqlBulkCopy.DestinationTableName = GetTableName(items[0]);

        foreach (var property in typeof(T).GetProperties().Where(x => x.GetCustomAttribute<ComputedAttribute>() == null && x.GetCustomAttribute<IdentityAttribute>() == null))
        {
            if (ignoreColumns.Contains(property.Name))
            {
                continue;
            }

            sqlBulkCopy.ColumnMappings.Add(property.Name, property.GetCustomAttribute<ColumnAttribute>()?.Name ?? property.Name);
        }

        return sqlBulkCopy;
    }

    /// <summary>
    /// 删除多条
    /// </summary>
    /// <param name="entities"></param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <returns></returns>
    public static bool Delete<T>(this IDbConnection connection, IEnumerable<T> entities, IDbTransaction transaction = null, int? commandTimeout = null)
    {
        return connection.Execute("", null, transaction, commandTimeout) > 0;
    }

    /// <summary>
    /// 删除多条
    /// </summary>
    /// <param name="entities"></param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <returns></returns>
    public static Task<bool> DeleteAsync<T>(this IDbConnection connection, IEnumerable<T> entities, IDbTransaction transaction = null, int? commandTimeout = null)
    {
        return true.ToTaskResult();
    }

    /// <summary>
    /// 获取一条
    /// </summary>
    /// <param name="id"></param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <returns></returns>
    public static T Get<T>(this IDbConnection connection, object id, IDbTransaction transaction = null, int? commandTimeout = null)
    {
        var sql = $@"SELECT * FROM {GetTableName<T>(default)} WHERE";
        return connection.QueryFirstOrDefault<T>(sql);
    }

    /// <summary>
    /// 获取所有实体
    /// </summary>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <returns></returns>
    public static IEnumerable<T> GetAll<T>(this IDbConnection connection, IDbTransaction transaction = null, int? commandTimeout = null)
    {
        return connection.Query<T>("");
    }

    /// <summary>
    /// 获取所有实体
    /// </summary>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <returns></returns>
    public static Task<IEnumerable<T>> GetAllAsync<T>(this IDbConnection connection, IDbTransaction transaction = null, int? commandTimeout = null)
    {
        return connection.QueryAsync<T>("");
    }

    /// <summary>
    /// 获取一条
    /// </summary>
    /// <param name="id"></param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <returns></returns>
    public static Task<T> GetAsync<T>(this IDbConnection connection, object id, IDbTransaction transaction = null, int? commandTimeout = null)
    {
        return connection.QueryFirstOrDefaultAsync<T>("id", null, transaction, commandTimeout);
    }

    public static long Insert<T>(this IDbConnection connection, T entity, IDbTransaction? transaction = null, int? commandTimeout = null) where T : class
    {
        var sql = $"INSERT INTO {GetTableName<T>(entity)} ({GetColumnNames<T>().JoinAsString(",")}) VALUES ({GetParameterNames<T>().JoinAsString(",")}); SELECT CAST(SCOPE_IDENTITY()  AS BIGINT) AS [id]";
        var result = connection.ExecuteScalar<long>(sql, entity, transaction, commandTimeout);

        var prop = entity.GetType().GetProperties().FirstOrDefault(x => x.GetCustomAttribute<KeyAttribute>() != null);

        if (prop != null)
        {
            if (prop.PropertyType == typeof(int))
            {
                prop.SetValue(entity, (int)result);
            }
            if (prop.PropertyType == typeof(long))
            {
                prop.SetValue(entity, result);
            }
        }

        return result;
    }

    public static async Task<long> InsertAsync<T>(this IDbConnection connection, T entity, IDbTransaction? transaction = null, int? commandTimeout = null) where T : class
    {


        //return (await SimpleCRUD.InsertAsync<long,T>(connection, entity, transaction, commandTimeout));

        var sql = $"INSERT INTO {GetTableName<T>(entity)} ({GetColumnNames<T>().JoinAsString(",")}) VALUES ({GetParameterNames<T>().JoinAsString(",")}); SELECT CAST(SCOPE_IDENTITY()  AS BIGINT) AS [id]";
        return await connection.QueryFirstAsync<long>(sql, entity, transaction, commandTimeout);
    }

    public static long InsertMany<T>(this IDbConnection connection, IEnumerable<T> entities, IDbTransaction? transaction = null, int? commandTimeout = null) where T : class
    {
        foreach (var item in entities)
        {
            Insert(connection, item);
        }

        return entities.Count();
    }


    public static async Task<long> InsertManyAsync<T>(this IDbConnection connection, IEnumerable<T> entities, IDbTransaction? transaction = null, int? commandTimeout = null) where T : class
    {
        foreach (var item in entities)
        {
            await InsertAsync(connection, item);
        }

        return entities.Count();
    }

    public static bool Update<T>(this IDbConnection connection, T entity, IDbTransaction transaction = null, int? commandTimeout = null)
    {
        return connection.Execute("") > 0;
    }

    public static async Task<bool> UpdateAsync<T>(this IDbConnection connection, T entity, IDbTransaction transaction = null, int? commandTimeout = null)
    {
        //connection.UpdateAsync()
        return (await SimpleCRUD.UpdateAsync(connection, entity, transaction, commandTimeout)) > 0;
        //return (await connection.UpdateAsync(entity));
    }

    private static List<string> GetColumnNames<T>()
    {
        var type = typeof(T);
        return type.GetProperties().Where(x => x.GetCustomAttribute<ComputedAttribute>() == null && x.GetCustomAttribute<KeyAttribute>() == null).Select(x => $"{x.GetCustomAttribute<ColumnAttribute>()?.Name ?? x.Name}").ToList();
    }

    private static List<string> GetParameterNames<T>()
    {
        var type = typeof(T);
        return type.GetProperties().Where(x => x.GetCustomAttribute<ComputedAttribute>() == null && x.GetCustomAttribute<KeyAttribute>() == null).Select(x => $"@{x.Name}").ToList();
    }

    private static string GetTableName<T>(T entity)
    {
        var type = typeof(T);
        var tableName = type.GetCustomAttribute<TableAttribute>()?.Name ?? type.Name;

        if (type.GetCustomAttribute<TableSplitableAttribute>() != null)
        {
            var coId = type.GetProperty("CoId")?.GetValue(entity);

            if (coId == null)
            {
                return tableName;
            }

            return tableName.Replace("_[n]", "_1");
            //return tableName + "_" + coId.ToInt32() % 1000;
        }

        return tableName;
    }
}

public class ComputedAttribute : Attribute
{
}